import pandas as pd
import matplotlib.pyplot as plt
import sqlalchemy
from sqlalchemy import create_engine
import numpy as np


# 1. 创建数据库连接
def create_db_connection():
    # 替换为您的实际数据库连接信息
    db_username = 'your_username'
    db_password = 'your_password'
    db_host = 'localhost'
    db_name = 'your_database'

    engine = create_engine(f'mysql+pymysql://{db_username}:{db_password}@{db_host}/{db_name}')
    return engine


# 2. 从数据库获取BWIC拍卖数据
def fetch_bwic_data(engine, bond_code):
    query = """
    SELECT 
        a.bond_code,
        b.bid_price AS market_price,
        b.bid_time AS created_time
    FROM 
        auction_assets a
    JOIN 
        auction_bids b ON a.id = b.auction_id
    WHERE 
        a.bond_code = %(bond_code)s
    ORDER BY 
        b.bid_time
    """

    # 使用pandas直接读取SQL数据
    df = pd.read_sql(query, engine, params={'bond_code': bond_code})
    return df


# 3. 处理数据并绘制价格曲线
def plot_bwic_price_curve(bwic_data, bond_code):
    # 数据预处理
    bwic_data.reset_index(drop=True, inplace=True)
    bwic_data.created_time = bwic_data.created_time.astype(str)

    # 创建图表
    plt.figure(figsize=(16, 10), dpi=80)

    # 绘制价格曲线
    plt.plot('created_time', 'market_price',
             data=bwic_data,
             color='tab:red',
             linewidth=2,
             label='BWIC Price')

    # 设置x轴刻度
    xtick_location = bwic_data.index.tolist()[::max(1, len(bwic_data) // 10)]  # 自动调整显示约10个标签
    xtick_labels = [pd.to_datetime(x).strftime('%H:%M') for x in
                    bwic_data.created_time.tolist()[::max(1, len(bwic_data) // 10)]]

    plt.xticks(ticks=xtick_location,
               labels=xtick_labels,
               rotation=45,
               fontsize=12,
               horizontalalignment='right',
               alpha=.7)

    # 设置y轴和标题
    plt.yticks(fontsize=12, alpha=.7)
    plt.title(f"BWIC Trade Price - Bond: {bond_code}", fontsize=22)

    # 添加网格和边框样式
    plt.grid(axis='both', alpha=.3)
    plt.gca().spines["top"].set_alpha(0.0)
    plt.gca().spines["bottom"].set_alpha(0.3)
    plt.gca().spines["right"].set_alpha(0.0)
    plt.gca().spines["left"].set_alpha(0.3)

    # 添加图例
    plt.legend(loc='upper left', fontsize=12)

    # 自动调整布局
    plt.tight_layout()
    plt.show()


# 4. 主执行函数
def main():
    try:
        # 创建数据库连接
        engine = create_db_connection()

        # 获取特定债券代码（示例：获取第一个债券的代码）
        bond_code_query = "SELECT bond_code FROM auction_assets LIMIT 1;"
        bond_code = pd.read_sql(bond_code_query, engine).iloc[0, 0]
        print(f"Analyzing BWIC data for bond: {bond_code}")

        # 获取该债券的BWIC数据
        bwic_data = fetch_bwic_data(engine, bond_code)

        if not bwic_data.empty:
            # 绘制价格曲线
            plot_bwic_price_curve(bwic_data, bond_code)
        else:
            print(f"No BWIC data found for bond: {bond_code}")

    except Exception as e:
        print(f"An error occurred: {str(e)}")
    finally:
        # 关闭数据库连接
        if 'engine' in locals():
            engine.dispose()


if __name__ == "__main__":
    main()